using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// UUM用户仓储实现
    /// </summary>
    public class UumUserRepository : IUumUserRepository
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<UumUserRepository> _logger;

        public UumUserRepository(
            IOracleConnectionFactory oracleConnectionFactory,
            ILogger<UumUserRepository> logger)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 根据用户名查询UUM用户信息
        /// </summary>
        public async Task<UumUser?> GetUserByUserNameAsync(string userName)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT 
                        ID as Id,
                        USER_NAME as UserName,
                        PEOPLE_CLASS as PeopleClass,
                        PEOPLE_IDENTIFIER as PeopleIdentifier,
                        PEOPLE_NAME as PeopleName,
                        WORK_DEPT as WorkDept,
                        WORK_POST as WorkPost,
                        WORK_PROFESSIONAL as WorkProfessional,
                        MOBILE as Mobile,
                        OFFICE_PHONE as OfficePhone,
                        EMAIL as Email,
                        USER_PASSWORD as UserPassword,
                        STATUS as Status,
                        EXPIRES as Expires,
                        DELETE_TIME as DeleteTime,
                        CREATOR as Creator,
                        CREATE_TIME as CreateTime,
                        MODIFIER as Modifier,
                        MODIFY_TIME as ModifyTime,
                        PINYIN_CODE as PinyinCode,
                        WUBI_CODE as WubiCode,
                        DUTY as Duty,
                        ID_NO as IdNo,
                        SUPER_LEADER as SuperLeader,
                        USERPHONE as UserPhone,
                        TASK_AMOUNT as TaskAmount,
                        USERTYPE as UserType,
                        PARTYBRANCH as PartyBranch
                    FROM UUM.UUM_USER 
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var result = await connection.QuerySingleOrDefaultAsync<UumUser>(sql, new { UserName = userName });
                
                _logger.LogInformation("查询UUM用户信息: UserName={UserName}, Found={Found}", userName, result != null);
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户信息失败: UserName={UserName}", userName);
                throw;
            }
        }

        /// <summary>
        /// 根据用户ID查询UUM用户信息
        /// </summary>
        public async Task<UumUser?> GetUserByIdAsync(int userId)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT 
                        ID as Id,
                        USER_NAME as UserName,
                        PEOPLE_CLASS as PeopleClass,
                        PEOPLE_IDENTIFIER as PeopleIdentifier,
                        PEOPLE_NAME as PeopleName,
                        WORK_DEPT as WorkDept,
                        WORK_POST as WorkPost,
                        WORK_PROFESSIONAL as WorkProfessional,
                        MOBILE as Mobile,
                        OFFICE_PHONE as OfficePhone,
                        EMAIL as Email,
                        USER_PASSWORD as UserPassword,
                        STATUS as Status,
                        EXPIRES as Expires,
                        DELETE_TIME as DeleteTime,
                        CREATOR as Creator,
                        CREATE_TIME as CreateTime,
                        MODIFIER as Modifier,
                        MODIFY_TIME as ModifyTime,
                        PINYIN_CODE as PinyinCode,
                        WUBI_CODE as WubiCode,
                        DUTY as Duty,
                        ID_NO as IdNo,
                        SUPER_LEADER as SuperLeader,
                        USERPHONE as UserPhone,
                        TASK_AMOUNT as TaskAmount,
                        USERTYPE as UserType,
                        PARTYBRANCH as PartyBranch
                    FROM UUM.UUM_USER 
                    WHERE ID = :UserId
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var result = await connection.QuerySingleOrDefaultAsync<UumUser>(sql, new { UserId = userId });
                
                _logger.LogInformation("查询UUM用户信息: UserId={UserId}, Found={Found}", userId, result != null);
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户信息失败: UserId={UserId}", userId);
                throw;
            }
        }

        /// <summary>
        /// 根据工号查询UUM用户信息
        /// </summary>
        public async Task<UumUser?> GetUserByPeopleIdentifierAsync(string peopleIdentifier)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT 
                        ID as Id,
                        USER_NAME as UserName,
                        PEOPLE_CLASS as PeopleClass,
                        PEOPLE_IDENTIFIER as PeopleIdentifier,
                        PEOPLE_NAME as PeopleName,
                        WORK_DEPT as WorkDept,
                        WORK_POST as WorkPost,
                        WORK_PROFESSIONAL as WorkProfessional,
                        MOBILE as Mobile,
                        OFFICE_PHONE as OfficePhone,
                        EMAIL as Email,
                        USER_PASSWORD as UserPassword,
                        STATUS as Status,
                        EXPIRES as Expires,
                        DELETE_TIME as DeleteTime,
                        CREATOR as Creator,
                        CREATE_TIME as CreateTime,
                        MODIFIER as Modifier,
                        MODIFY_TIME as ModifyTime,
                        PINYIN_CODE as PinyinCode,
                        WUBI_CODE as WubiCode,
                        DUTY as Duty,
                        ID_NO as IdNo,
                        SUPER_LEADER as SuperLeader,
                        USERPHONE as UserPhone,
                        TASK_AMOUNT as TaskAmount,
                        USERTYPE as UserType,
                        PARTYBRANCH as PartyBranch
                    FROM UUM.UUM_USER 
                    WHERE PEOPLE_IDENTIFIER = :PeopleIdentifier
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var result = await connection.QuerySingleOrDefaultAsync<UumUser>(sql, new { PeopleIdentifier = peopleIdentifier });
                
                _logger.LogInformation("查询UUM用户信息: PeopleIdentifier={PeopleIdentifier}, Found={Found}", peopleIdentifier, result != null);
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户信息失败: PeopleIdentifier={PeopleIdentifier}", peopleIdentifier);
                throw;
            }
        }

        /// <summary>
        /// 检查用户名是否存在
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>是否存在</returns>
        public async Task<bool> UserNameExistsAsync(string userName)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                var sql = @"
                    SELECT COUNT(1) 
                    FROM UUM.UUM_USER 
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var count = await connection.QuerySingleAsync<int>(sql, new { UserName = userName });
                return count > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "检查用户名是否存在失败，用户名: {UserName}", userName);
                throw;
            }
        }

        /// <summary>
        /// 更新用户信息（仅更新指定字段）
        /// </summary>
        /// <param name="request">更新请求</param>
        /// <returns>是否更新成功</returns>
        public async Task<bool> UpdateUserAsync(UumUserUpdateRequest request)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                var sql = @"
                    UPDATE UUM.UUM_USER 
                    SET TASK_AMOUNT = :TaskAmount,
                        USERTYPE = :UserType,
                        PARTYBRANCH = :PartyBranch,
                        USERPHONE = :UserPhone,
                        MODIFY_TIME = SYSDATE
                    WHERE ID = :UserId
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var rowsAffected = await connection.ExecuteAsync(sql, new
                {
                    TaskAmount = request.TaskAmount,
                    UserType = request.UserType,
                    PartyBranch = request.PartyBranch,
                    UserPhone = request.UserPhone,
                    UserId = request.Id
                });

                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新用户信息失败，用户ID: {UserId}", request.Id);
                throw;
            }
        }

        /// <summary>
        /// 搜索用户信息
        /// </summary>
        /// <param name="request">搜索请求</param>
        /// <returns>用户信息列表</returns>
        public async Task<IEnumerable<UumUserSearchItem>> SearchUsersAsync(UumUserSearchRequest request)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();

                // 基础条件
                whereConditions.Add("(DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)");
                whereConditions.Add("STATUS != 'D'");

                // 动态添加搜索条件
                if (!string.IsNullOrWhiteSpace(request.PeopleName))
                {
                    whereConditions.Add("PEOPLE_NAME LIKE :PeopleName");
                    parameters.Add("PeopleName", $"%{request.PeopleName}%");
                }

                if (!string.IsNullOrWhiteSpace(request.UserName))
                {
                    whereConditions.Add("USER_NAME LIKE :UserName");
                    parameters.Add("UserName", $"%{request.UserName}%");
                }

                if (!string.IsNullOrWhiteSpace(request.UserType))
                {
                    whereConditions.Add("USERTYPE = :UserType");
                    parameters.Add("UserType", request.UserType);
                }

                if (!string.IsNullOrWhiteSpace(request.PartyBranch))
                {
                    whereConditions.Add("PARTYBRANCH = :PartyBranch");
                    parameters.Add("PartyBranch", request.PartyBranch);
                }

                var sql = $@"
                    SELECT 
                        ID as Id,
                        USER_NAME as UserName,
                        PEOPLE_NAME as PeopleName,
                        ID_NO as IdNo,
                        USERPHONE as UserPhone,
                        TASK_AMOUNT as TaskAmount,
                        USERTYPE as UserType,
                        PARTYBRANCH as PartyBranch
                    FROM UUM.UUM_USER 
                    WHERE {string.Join(" AND ", whereConditions)}
                    ORDER BY ID";

                var result = await connection.QueryAsync<UumUserSearchItem>(sql, parameters);
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "搜索用户信息失败");
                throw;
            }
        }
    }
}